package com.pls.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.pls.domain.Employee;
import com.pls.domain.EmployeeWorkstation;
import com.pls.domain.Project;
import com.pls.domain.Seat;
import com.pls.utilities.ConnectionManager;

public class EmployeeDAOImpl implements EmployeeDAO {

	Logger logger = Logger.getLogger(EmployeeDAOImpl.class);

	private EmployeeWorkstation empwork;
	private List<Employee> empWildCard;
	private CallableStatement proc = null;
	private ResultSet rs = null;
	private ConnectionManager conManager;
	private Connection connection = null;

	// Stored procedures
	// Move to constants
	private final static String USP_Get_Employee_WildCard_By_Name = "{ call GetEmployeeWildCardByName(?) }";
	private final static String USP_Get_Employee_WildCard_By_Id = "{ call GetEmployeeWildCardById(?) }";
	private final static String USP_Get_Employee_Detail_By_Name = "{ call GetEmployeeDetailByFirstAndLastName(?,?) }";
	private final static String USP_Get_EmployeeWorkstationMap_By_Name = "{ call GetEmployeeWorkstationMapByFirstAndLastName(?,?) }";

	@Override
	public EmployeeWorkstation getEmployeeDetailsByName(String firstname,
			String lastname) throws Exception {
		empwork = new EmployeeWorkstation();
		try {
			// Method enter log
			logger.debug("Method searchEmployeeByName entered.");

			conManager = ConnectionManager.getInstance();
			connection = conManager.getConnection();
			proc = connection.prepareCall(USP_Get_Employee_Detail_By_Name);
			proc.setString(1, firstname);
			proc.setString(2, lastname);

			rs = proc.executeQuery();

			while (rs.next()) {
				Employee emp = new Employee();
				Seat seat = new Seat();
				emp.setEmployeeId(Integer.parseInt(rs.getString("employeeId")));
				emp.setFirstName(rs.getString("firstName"));
				emp.setLastName(rs.getString("lastName"));
				emp.setEmailAddress(rs.getString("emailAddress"));
				emp.setPositionCode(rs.getString("positionCode"));
				seat.setSeatId(Integer.parseInt(rs.getString("seatId")));
				seat.setSeatCode(rs.getString("seatCode"));
				seat.setQuadrant(rs.getString("quadrant"));
				seat.setLocationDetailId(rs.getString("locationDetailId"));
				seat.setLocalNumber(Integer.parseInt(rs
						.getString("localNumber")));
				seat.setSeatTypeId(Integer.parseInt(rs.getString("seatTypeId")));
				seat.setSeatType(rs.getString("seatType"));
				seat.setSeatColumn(Integer.parseInt(rs.getString("seatColumn")));
				seat.setSeatRow(Integer.parseInt(rs.getString("seatRow")));
				empwork.setEmployee(emp);
				empwork.setSeatDetails(seat);
			}

			rs.close();
			// Method exit log

		} catch (SQLException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (ClassNotFoundException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (Exception e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
				logger.error(this.getClass() + "- Exception:" + e.getClass()
						+ "-" + e.getMessage());
				throw e;
			}
			connection.close();
		}

		logger.debug("Method searchByEmployeeName successfully executed.");
		return empwork;
	}

	@Override
	public EmployeeWorkstation searchByEmployeeId(String employee)
			throws Exception {
		// Method enter log
		logger.debug("Method searchByEmployeeId entered.");

		empwork = new EmployeeWorkstation();
		try {
			conManager = ConnectionManager.getInstance();
			connection = conManager.getConnection();
			proc = connection
					.prepareCall("{ call GetEmployeeDetailByEmployeeID(?) }");
			proc.setInt(1, Integer.parseInt(employee));

			rs = proc.executeQuery();
			// empwork = populateEmployee(rs, empwork);

			while (rs.next()) {
				Employee emp = new Employee();
				Seat seat = new Seat();
				emp.setEmployeeId(Integer.parseInt(rs.getString("employeeId")));
				emp.setFirstName(rs.getString("firstName"));
				emp.setLastName(rs.getString("lastName"));
				emp.setEmailAddress(rs.getString("emailAddress"));
				emp.setPositionCode(rs.getString("positionCode"));
				seat.setSeatId(Integer.parseInt(rs.getString("seatId")));
				seat.setSeatCode(rs.getString("seatCode"));
				// seat.setSeatQuadrant(rs.getString("quadrant"));
				seat.setLocationDetailId(rs.getString("locationDetailId"));
				seat.setLocalNumber(Integer.parseInt(rs
						.getString("localNumber")));
				seat.setSeatTypeId(Integer.parseInt(rs.getString("seatTypeId")));
				seat.setSeatType(rs.getString("seatType"));
				seat.setSeatColumn(Integer.parseInt(rs.getString("seatColumn")));
				seat.setSeatRow(Integer.parseInt(rs.getString("seatRow")));
				empwork.setEmployee(emp);
				empwork.setSeatDetails(seat);
			}

			rs.close();
			// Method exit log
			logger.debug("Method searchByEmployeeId successfully executed.");

		} catch (SQLException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (ClassNotFoundException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (Exception e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
				logger.error("- Exception:" + e.getClass()
						+ "-" + e.getMessage());
				throw e;
			}
			connection.close();
		}
		return empwork;
	}

	@Override
	public Employee searchByProject(String employee) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Employee> getAllEmployee() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Project> getEmployeeDetails(String employeeId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Project> getProjectList(String employeeId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * Returns a list of employees based on a wild card search by name
	 */
	@Override
	public List<Employee> getEmployeeWildCardByName(String employeeName)
			throws Exception {
		// Method enter log
		logger.debug("Method getEmployeeWildCardByName entered.");
		empWildCard = new ArrayList<Employee>();

		try {
			conManager = ConnectionManager.getInstance();
			connection = conManager.getConnection();
			proc = connection.prepareCall(USP_Get_Employee_WildCard_By_Name);
			proc.setString(1, employeeName);

			rs = proc.executeQuery();
			// empwork = populateEmployee(rs, empwork);

			while (rs.next()) {
				Employee emp = new Employee();
				emp.setEmployeeId(Integer.parseInt(rs.getString("employee_Id")));
				emp.setFirstName(rs.getString("first_Name"));
				emp.setLastName(rs.getString("last_Name"));
				emp.setEmailAddress(rs.getString("email_Add"));

				empWildCard.add(emp);
			}

			rs.close();
			logger.debug("Method getEmployeeWildCardByName successfully executed.");

		} catch (SQLException e) {
			logger.error(this.getClass() + "-" + e.getClass() + "-");
			throw e;
		} catch (ClassNotFoundException e) {
			// Do error logs here
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
			}
			connection.close();
		}
		return empWildCard;
	}

	@Override
	public List<Employee> getEmployeeWildCardById(int employeeId)
			throws SQLException, ClassNotFoundException, Exception {
		empWildCard = new ArrayList<Employee>();

		try {
			conManager = ConnectionManager.getInstance();
			connection = conManager.getConnection();
			proc = connection.prepareCall(USP_Get_Employee_WildCard_By_Id);
			proc.setInt(1, employeeId);

			rs = proc.executeQuery();
			// empwork = populateEmployee(rs, empwork);
			mapResultToEmployeeWildCard(rs);
			rs.close();

		} catch (SQLException e) {
			// Do error logs here
			throw e;
		} catch (ClassNotFoundException e) {
			// Do error logs here
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
			}
			connection.close();
		}
		return empWildCard;
	}

	public List<EmployeeWorkstation> getEmployeeWorkStationMapByFirstNameAndLastName(String firstName, String lastName) throws Exception{
		logger.debug("DAO Method getEmployeeWorkStationMapByFirstNameAndLastName entered.");

		List<EmployeeWorkstation> workStationList = new ArrayList<EmployeeWorkstation>();
		
		try {
			conManager = ConnectionManager.getInstance();
			connection = conManager.getConnection();
			proc = connection
					.prepareCall(USP_Get_EmployeeWorkstationMap_By_Name);
			proc.setString(1, firstName);
			proc.setString(2, lastName);

			rs = proc.executeQuery();

			while (rs.next()) {
				EmployeeWorkstation employeeWorkStation = new EmployeeWorkstation();
				Employee emp = new Employee();
				Seat seat = new Seat();
				Project project = new Project();
				
				emp.setEmployeeId(Integer.parseInt((rs.getString("employeeId") == null ? "0" : rs.getString("employeeId"))));
				emp.setFirstName(rs.getString("firstName"));
				emp.setLastName(rs.getString("lastName"));
				emp.setEmailAddress(rs.getString("emailAddress"));
				emp.setPositionCode(rs.getString("positionCode"));
				seat.setSeatId(Integer.parseInt(rs.getString("seatId")));
				seat.setSeatCode(rs.getString("seatCode"));
				// seat.setSeatQuadrant(rs.getString("quadrant"));
				seat.setLocationDetailId(rs.getString("locationDetailId"));
				seat.setLocalNumber(Integer.parseInt(rs
						.getString("localNumber")));
				seat.setSeatTypeId(Integer.parseInt(rs.getString("seatTypeId")));
				seat.setSeatType(rs.getString("seatType"));
				seat.setSeatColumn(Integer.parseInt(rs.getString("seatColumn")));
				seat.setSeatRow(Integer.parseInt(rs.getString("seatRow")));
				
				//map to workstation
				employeeWorkStation.setEmployee(emp);
				employeeWorkStation.setSeatDetails(seat);
				
				//add to list
				workStationList.add(employeeWorkStation);
			}

			rs.close();
			// Method exit log
			logger.debug("DAO Method getEmployeeWorkStationMapByFirstNameAndLastName successfully executed.");

		} catch (SQLException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (ClassNotFoundException e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} catch (Exception e) {
			logger.error("- Exception:" + e.getClass() + "-"
					+ e.getMessage());
			throw e;
		} finally {
			try {
				proc.close();
			} catch (SQLException e) {
				logger.error("- Exception:" + e.getClass()
						+ "-" + e.getMessage());
				throw e;
			}
			connection.close();
		}
		return workStationList;
	}
	
	
	/*
	 * Generic method for mapping a result set to the employee wild card list
	 */
	private void mapResultToEmployeeWildCard(ResultSet rs)
			throws NumberFormatException, SQLException {
		while (rs.next()) {
			Employee emp = new Employee();
			emp.setEmployeeId(Integer.parseInt(rs.getString("employee_Id")));
			emp.setFirstName(rs.getString("first_Name"));
			emp.setLastName(rs.getString("last_Name"));
			emp.setEmailAddress(rs.getString("email_Add"));

			empWildCard.add(emp);
		}
	}

	// private EmployeeWorkstation populateEmployee(ResultSet rs,
	// EmployeeWorkstation empwork) {
	//
	// try {
	//
	// Employee employee = empwork.getEmployee();
	// Seat seat = empwork.getSeatDetails();
	// while (rs.next()) {
	// // employee.setEmployeeId(Integer.parseInt(rs.getString("employeeId")));
	// employee.setFirstName(rs.getString("firstName"));
	// employee.setLastName(rs.getString("lastName"));
	// employee.setEmailAddress(rs.getString("emailAddress"));
	// employee.setPositionCode(rs.getString("positionCode"));
	// // seat.setSeatId(Integer.parseInt(rs.getString("seatId")));
	// seat.setSeatCode(rs.getString("seatCode"));
	// // seat.setSeatQuadrant(rs.getString("quadrant"));
	// seat.setLocationDetailId(rs.getString("locationDetailId"));
	// // seat.setLocalNumber(Integer.parseInt(rs.getString("localNumber")));
	// // seat.setSeatTypeId(Integer.parseInt(rs.getString("seatTypeId")));
	// seat.setSeatType(rs.getString("seatType"));
	// // seat.setSeatColumn(Integer.parseInt(rs.getString("seatColumn")));
	// // seat.setSeatRow(Integer.parseInt(rs.getString("seatRow")));
	//
	// }
	// } catch (NumberFormatException e) { // TODO Auto-generated catch block
	// e.printStackTrace();
	// } catch (SQLException e) { // TODO Auto-generated
	// e.printStackTrace();
	// }
	//
	// return emp;
	// }
}
